package com.be.beadmin.design.impl;

import cn.hutool.core.util.ObjectUtil;
import com.be.beadmin.basicData.dto.output.UserEx;
import com.be.beadmin.basicData.entity.MaterialInfo;
import com.be.beadmin.basicData.repository.MaterialInfoRepository;
import com.be.beadmin.basicData.service.UserService;
import com.be.beadmin.common.entity.FilderExpressEntity;
import com.be.beadmin.common.entity.PageEntity;
import com.be.beadmin.common.entity.SortExpressEntity;
import com.be.beadmin.common.exception.BeadminException;
import com.be.beadmin.common.utils.JpaQueryBuilder;
import com.be.beadmin.common.utils.JpaQueryBuilderNew;
import com.be.beadmin.common.utils.JpaSortBuilder;
import com.be.beadmin.design.dto.input.ProductBomOverviewDto;
import com.be.beadmin.design.dto.output.BomReviewEx;
import com.be.beadmin.design.dto.output.ProductBomOverviewEx;
import com.be.beadmin.design.entity.*;
import com.be.beadmin.design.mapper.BomReviewMapperEx;
import com.be.beadmin.design.mapper.ProductBomMapper;
import com.be.beadmin.design.mapper.ProductBomOverviewMapper;
import com.be.beadmin.design.mapper.ProductBomOverviewMapperEx;
import com.be.beadmin.design.repository.BomReviewRepository;
import com.be.beadmin.design.repository.ProductBomOverviewRepository;
import com.be.beadmin.design.repository.ProductBomRepository;
import com.be.beadmin.design.repository.StandFileInfoRepository;
import com.be.beadmin.design.service.ProductBomOverviewService;
import com.be.beadmin.project.entity.BatchProduct;
import com.be.beadmin.project.entity.Info;
import com.be.beadmin.project.entity.TeamInfo;
import com.be.beadmin.project.repository.BatchProductRepository;
import com.be.beadmin.project.repository.InfoRepository;
import com.be.beadmin.project.repository.TeamInfoRepository;
import com.be.beadmin.service.entity.NotifyInfo;
import com.be.beadmin.service.impl.MinioService;
import com.be.beadmin.service.repository.NotifyInfoRepository;
import com.be.beadmin.service.repository.NotifyTemplateInfoRepository;
import com.be.beadmin.service.service.NotifyTemplateInfoService;
import com.be.beadmin.service.service.SerialNumberService;
import lombok.RequiredArgsConstructor;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.persistence.criteria.Order;
import javax.persistence.criteria.Predicate;
import javax.servlet.http.HttpServletRequest;
import java.io.IOException;
import java.io.InputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @description 服务实现
 * @author zmy
 * @date 2023-02-16
 **/
@Service
@RequiredArgsConstructor
public class ProductBomOverviewServiceImpl implements ProductBomOverviewService {

    @Autowired
    private  ProductBomOverviewRepository productBomOverviewRepository;
    @Autowired
    private  ProductBomOverviewMapper productBomOverviewMapper;
    @Autowired
    private  ProductBomOverviewMapperEx productBomOverviewExMapper;
    @Autowired
    private SerialNumberService serialNumberService;
    @Autowired
    private ProductBomRepository productBomRepository;
    @Autowired
    private ProductBomMapper productBomMapper;

    @Autowired
    private MinioService minioService;
    private final BatchProductRepository batchProductRepository;

    @Autowired
    private UserService userService;
    private final TeamInfoRepository teamInfoRepository;
    private final BomReviewRepository bomReviewRepository;
    private final InfoRepository infoRepository;

    @Autowired
    private StandFileInfoRepository standFileInfoRepository;


    @Autowired
    private NotifyTemplateInfoService notifyTemplateInfoService;

    @Autowired
    private NotifyTemplateInfoRepository notifyTemplateInfoRepository;

    @Autowired
    private NotifyInfoRepository notifyInfoRepository;

    @Autowired
    private MaterialInfoRepository materialInfoRepository;


    @Override
    public Page<ProductBomOverviewEx> getProductBomOverviewByPage(PageEntity pageEntity) {
        Pageable pageable = PageRequest.of(
                pageEntity.getCurrent() -1,
                pageEntity.getPageSize());
        Page<ProductBomOverview> page = productBomOverviewRepository.findAll((root, criteriaQuery, criteriaBuilder) -> {
                    List<Predicate> list = new ArrayList<>();
                    Predicate pre1 =   JpaQueryBuilder.getPredicate(root, pageEntity.getFilters(), criteriaBuilder);
                    list.add(pre1);
                    Predicate pre2 = criteriaBuilder.equal(root.get(ProductBomOverview_.delete), 0); //过滤为0
                    list.add(pre2);
                    Predicate[] p = new Predicate[list.size()];
                    List<Order> orders = new ArrayList<>();
                    if(pageEntity.getSorters() != null && pageEntity.getSorters().size() > 0) {
                        orders = JpaSortBuilder.getPredicate(root, pageEntity.getSorters(), criteriaBuilder);
                        criteriaQuery.orderBy(orders);
                    } else {
//                        criteriaQuery.orderBy(criteriaBuilder.desc(root.get(ProductBomOverview_.createTime).as(Date.class)));
                        criteriaQuery.orderBy(criteriaBuilder.asc(root.get(ProductBomOverview_.bomSequence).as(String.class)));

                    }
                    return  criteriaBuilder.and(list.toArray(p));
                }
                , pageable);
        Page<ProductBomOverviewEx> page1 = page.map(productBomOverviewExMapper::toEntity);
        return page1;

    }



    @Override
    public void addProductBomOverview(ProductBomOverviewDto productBomOverviewDto) {

//        ProductBomOverview result = productBomOverviewRepository.findProductBomOverviewByIdAndDelete(productBomOverviewDto.getId(), 0);
//        if(ObjectUtil.isNotEmpty(result)) {
//            throw new BeadminException("ProductBomOverview不存在,请重新查询");
//        }
        //首先获取BomCode
        ProductBom productBom = productBomRepository.findProductBomByProductCode(productBomOverviewDto.getProductCode());
        if(productBom==null){
            productBom = new ProductBom();
            productBom.setProductCode(productBomOverviewDto.getProductCode());

        }

        //如果没有记录，就生成BomCode
        if (productBom.getBomCode()==null){
            String nextSnByCode = serialNumberService.getNextSnByCode("BomCode");
            productBom.setBomCode(nextSnByCode);
            productBomRepository.save(productBom);
        }



        productBomOverviewDto.setBomCode(productBom.getBomCode());

        ProductBomOverview productBomOverview = productBomOverviewMapper.toEntity(productBomOverviewDto);
        productBomOverviewRepository.save(productBomOverview);
        //
    }

    @Override
    public void updateProductBomOverview(ProductBomOverviewDto productBomOverviewDto) {
        ProductBomOverview productBomOverview = productBomOverviewRepository.findProductBomOverviewById(productBomOverviewDto.getId());
        productBomOverview = productBomOverviewMapper.toEntity(productBomOverviewDto);
        productBomOverviewRepository.save(productBomOverview);
    }


    @Override
    public void delProductBomOverview(String id) {
        ProductBomOverview productBomOverview = productBomOverviewRepository.findProductBomOverviewById(id);
        productBomOverview.setDelete(1);
        productBomOverviewRepository.save(productBomOverview);
    }

    @Override
    public void delProductBomOverviews (List<String> ids) { }


    /**
     * 上传BOM文件
     * @param batchProductId
     * @param file
     * @throws IOException
     */
    @Transactional
    public void addBomByTemplate(HttpServletRequest request, String batchProductId, MultipartFile file) throws IOException {
        String bomCode = serialNumberService.getNextSnByCode("BomCode");
        //上传的人
        UserEx userEx = userService.viewBaseInfo(request);
        String userId = userEx.getId();
        String userName = userEx.getName();

        BatchProduct batchProductById = batchProductRepository.findBatchProductById(batchProductId);

        String projectCode = batchProductById.getProductCode().substring(0, batchProductById.getProductCode().indexOf("."));
        bomCode = projectCode + "." + "002." + bomCode;
        batchProductById.setBomId(bomCode);


        batchProductById.setBomTime(new Date());
        if (batchProductById.getReviewState().equals(2)) {
            batchProductById.setReviewState(0);

        }
        batchProductRepository.save(batchProductById);

        InputStream inputStream = file.getInputStream();
        //这里判断一下excel是xls还是xlsx
        String filename = file.getOriginalFilename();
        String suffix = filename.substring(filename.lastIndexOf(".") + 1);
        Workbook workbook = null;
        if (suffix.equals("xls")) {
            workbook = new HSSFWorkbook(inputStream);
        } else if (suffix.equals("xlsx")) {
            workbook = new XSSFWorkbook(inputStream);
        } else {
            throw new RuntimeException("请使用正确的Excel模板！");
        }
        Sheet sheet = workbook.getSheetAt(0);

        //查看这个柜体是否上传过bom
        List<ProductBomOverview> oldBom = productBomOverviewRepository.findProductBomOverviewsByProductCodeAndDelete(batchProductById.getProductCode(), 0);
        DataFormatter formatter = new DataFormatter();
        //如果不为空，全部清空
        if (!oldBom.isEmpty()) {
            for (ProductBomOverview p : oldBom) {
                productBomOverviewRepository.deleteById(p.getId());
            }
        }
        List<ProductBomOverview> lp = new ArrayList<>();
        //清空后添加,之后可以将添加单独写个函数，解耦
        // 遍历行并将数据保存到数据库中
        for (Row row : sheet) {
            // 跳过表头
            if (row.getRowNum() == 0) {
                continue;
            }
            ProductBomOverview productBomOverview = new ProductBomOverview();
            //String materialCode = serialNumberService.getNextSnByCode("MaterialCode");

            //productBomOverview.setMaterialCode(materialCode);
            productBomOverview.setBomCode(bomCode);
            //            productBomOverview.setProductCode(batchProductById.getProductCode());
            productBomOverview.setProductCode(batchProductById.getProductCode());


            //System.out.println(formatter.formatCellValue(row.getCell(0)));
            if (row.getCell(0) == null || row.getCell(0).getCellType().equals(CellType.BLANK)) {
                throw new BeadminException("第" + row.getRowNum() + "行【物料编码】字段不能为空");
            } else {
                if (row.getCell(0).getCellType() == CellType.STRING)
                    productBomOverview.setMaterialCode(formatter.formatCellValue(row.getCell(0)));
                else if (row.getCell(0).getCellType() == CellType.NUMERIC) {
                    productBomOverview.setMaterialCode(formatter.formatCellValue(row.getCell(0)));
                }
            }

            if (row.getCell(1) != null) {
                if (row.getCell(1).getCellType() == CellType.STRING)
                    productBomOverview.setMaterialName(row.getCell(1).getStringCellValue());
                else if (row.getCell(1).getCellType() == CellType.NUMERIC)
                    productBomOverview.setMaterialName(String.valueOf(row.getCell(1).getNumericCellValue()));
            }
            if (row.getCell(2) == null || row.getCell(2).getCellType().equals(CellType.BLANK)) {
                throw new BeadminException("第" + row.getRowNum() + "行【物料规格】字段不能为空");
            } else {
                if (row.getCell(2).getCellType() == CellType.STRING)
                    productBomOverview.setMaterialSpec(row.getCell(2).getStringCellValue());
                else if (row.getCell(2).getCellType() == CellType.NUMERIC)
                    productBomOverview.setMaterialSpec(String.valueOf(row.getCell(2).getNumericCellValue()));
            }


            if (row.getCell(3) == null || row.getCell(3).getCellType().equals(CellType.BLANK)) {
                throw new BeadminException("第" + row.getRowNum() + "行【数量】字段不能为空");
            } else {
                if (row.getCell(3).getCellType() == CellType.NUMERIC) {
                    if ((int) row.getCell(3).getNumericCellValue() < 0) {
                        throw new BeadminException("物料数量不能为负！");
                    }
                    productBomOverview.setMaterialQty(row.getCell(3).getNumericCellValue());
                } else if (row.getCell(3).getCellType() == CellType.STRING) {
                    if ((int) row.getCell(3).getNumericCellValue() < 0) {
                        throw new BeadminException("物料数量不能为负！");
                    }
                    productBomOverview.setMaterialQty(Double.parseDouble(row.getCell(3).getStringCellValue()));
                }
            }

            if (row.getCell(4) == null || row.getCell(4).getCellType().equals(CellType.BLANK)) {
                throw new BeadminException("第" + row.getRowNum() + "行【单位】字段不能为空");
            } else {
                if (row.getCell(4).getCellType() == CellType.STRING)
                    productBomOverview.setUnit(row.getCell(4).getStringCellValue());
                else if (row.getCell(4).getCellType() == CellType.NUMERIC) {
                    productBomOverview.setUnit(String.valueOf(row.getCell(4).getNumericCellValue()));
                }
            }

            if (row.getCell(5) == null || row.getCell(5).getCellType().equals(CellType.BLANK)) {
                throw new BeadminException("第" + row.getRowNum() + "行【位置号】字段不能为空");
            } else {
                if (row.getCell(5).getCellType() == CellType.STRING)
                    productBomOverview.setPositionNo(row.getCell(5).getStringCellValue());
                else if (row.getCell(5).getCellType() == CellType.NUMERIC) {
                    productBomOverview.setPositionNo(String.valueOf(row.getCell(5).getNumericCellValue()));
                } else if (row.getCell(5).getCellType() == CellType.FORMULA) {
                    productBomOverview.setPositionNo(String.valueOf(row.getCell(5).getCellFormula()));
                }
            }


            if (row.getCell(6) != null) {
                if (row.getCell(6).getCellType() == CellType.STRING)
                    productBomOverview.setBrand(row.getCell(6).getStringCellValue());
                else if (row.getCell(6).getCellType() == CellType.NUMERIC)
                    productBomOverview.setBrand(String.valueOf(row.getCell(6).getNumericCellValue()));
            }
            if (row.getCell(7) != null) {
                if (row.getCell(7).getCellType() == CellType.STRING)
                    productBomOverview.setMaterialClassification(row.getCell(7).getStringCellValue());
                else if (row.getCell(7).getCellType() == CellType.NUMERIC)
                    productBomOverview.setMaterialClassification(String.valueOf(row.getCell(7).getNumericCellValue()));
            }
            if (row.getCell(8) != null) {
                if (row.getCell(8).getCellType() == CellType.STRING)
                    productBomOverview.setMaterialType(row.getCell(8).getStringCellValue());
                else if (row.getCell(8).getCellType() == CellType.NUMERIC)
                    productBomOverview.setMaterialType(String.valueOf(row.getCell(8).getNumericCellValue()));
            }
            if (row.getCell(9) != null) {
                if (row.getCell(9).getCellType() == CellType.STRING)
                    productBomOverview.setIsJia(row.getCell(9).getStringCellValue());
                else if (row.getCell(9).getCellType() == CellType.NUMERIC)
                    productBomOverview.setIsJia(String.valueOf(row.getCell(9).getNumericCellValue()));
            }


            productBomOverview.setVersion(1);
            productBomOverview.setVersionSecond(0);
            productBomOverview.setIsMax(1);
            productBomOverview.setUserId(userId);
            productBomOverview.setUserName(userName);
            for (ProductBomOverview p : lp) {
                if (p.getMaterialCode().equals(productBomOverview.getMaterialCode()) && p.getMaterialName().equals(productBomOverview.getMaterialName())) {
                    throw new BeadminException("第" + row.getRowNum() + "行与某一行的物料编码、物料名称重复！");
                }
            }
            lp.add(productBomOverview);


            //  判断该物料编码是否存在于主数据中，没有则新增至其中

            MaterialInfo materialInfo = materialInfoRepository.findMaterialInfoByMaterialCodeAndDelete(productBomOverview.getMaterialCode(), 0);
            if (materialInfo == null){

                MaterialInfo materialInfo1 =new MaterialInfo();
                materialInfo1.setMaterialCode(productBomOverview.getMaterialCode());
                materialInfo1.setMaterialName(productBomOverview.getMaterialName());
                materialInfo1.setMaterialSpec(productBomOverview.getMaterialSpec());
//                materialInfo1.setPara();
                materialInfo1.setUnit(productBomOverview.getUnit());
                materialInfo1.setCategory(productBomOverview.getMaterialClassification());
                //                materialInfo1.setBarCode();
                materialInfo1.setBrand(productBomOverview.getBrand());
                materialInfo1.setEffective(1);
                materialInfo1.setMaterialProperty("生产加工");
                materialInfoRepository.save(materialInfo1);
                }
            }

        productBomOverviewRepository.saveAll(lp);

        }


        /**
        if(oldBom.isEmpty()) {
            List<ProductBomOverview> first = new ArrayList<>();
            // 遍历行并将数据保存到数据库中
            for (Row row : sheet) {
                // 跳过表头
                if (row.getRowNum() == 0) {
                    continue;
                }
                ProductBomOverview productBomOverview = new ProductBomOverview();
                //String materialCode = serialNumberService.getNextSnByCode("MaterialCode");

                //productBomOverview.setMaterialCode(materialCode);
                productBomOverview.setBomCode(bomCode);
                //            productBomOverview.setProductCode(batchProductById.getProductCode());
                productBomOverview.setProductCode(batchProductById.getProductCode());


                    //System.out.println(formatter.formatCellValue(row.getCell(0)));
                    if(row.getCell(0)==null || row.getCell(0).getCellType().equals(CellType.BLANK)){
                        throw new BeadminException("第"+row.getRowNum()+"行【物料编码】字段不能为空");
                    }else{
                        if (row.getCell(0).getCellType() == CellType.STRING)
                            productBomOverview.setMaterialCode(formatter.formatCellValue(row.getCell(0)));
                        else if (row.getCell(0).getCellType() == CellType.NUMERIC) {
                            productBomOverview.setMaterialCode(formatter.formatCellValue(row.getCell(0)));
                        }
                    }

                    if (row.getCell(1) != null) {
                        if (row.getCell(1).getCellType() == CellType.STRING)
                            productBomOverview.setMaterialName(row.getCell(1).getStringCellValue());
                        else if (row.getCell(1).getCellType() == CellType.NUMERIC)
                            productBomOverview.setMaterialName(String.valueOf(row.getCell(1).getNumericCellValue()));
                    }
                if(row.getCell(2)==null || row.getCell(2).getCellType().equals(CellType.BLANK)){
                    throw new BeadminException("第"+row.getRowNum()+"行【物料规格】字段不能为空");
                }else {
                    if (row.getCell(2).getCellType() == CellType.STRING)
                        productBomOverview.setMaterialSpec(row.getCell(2).getStringCellValue());
                    else if (row.getCell(2).getCellType() == CellType.NUMERIC)
                        productBomOverview.setMaterialSpec(String.valueOf(row.getCell(2).getNumericCellValue()));
                }


                if(row.getCell(3)==null || row.getCell(3).getCellType().equals(CellType.BLANK)){
                    throw new BeadminException("第"+row.getRowNum()+"行【数量】字段不能为空");
                }else{
                    if (row.getCell(3).getCellType() == CellType.NUMERIC) {
                        if ((int) row.getCell(3).getNumericCellValue() < 0) {
                            throw new BeadminException("物料数量不能为负！");
                        }
                        productBomOverview.setMaterialQty(row.getCell(3).getNumericCellValue());
                    } else if (row.getCell(3).getCellType() == CellType.STRING) {
                        if ((int) row.getCell(3).getNumericCellValue() < 0) {
                            throw new BeadminException("物料数量不能为负！");
                        }
                        productBomOverview.setMaterialQty(Double.parseDouble(row.getCell(3).getStringCellValue()));
                    }
                }

                if(row.getCell(4)==null || row.getCell(4).getCellType().equals(CellType.BLANK)){
                    throw new BeadminException("第"+row.getRowNum()+"行【单位】字段不能为空");
                }else{
                    if (row.getCell(4).getCellType() == CellType.STRING)
                        productBomOverview.setUnit(row.getCell(4).getStringCellValue());
                    else if (row.getCell(4).getCellType() == CellType.NUMERIC) {
                        productBomOverview.setUnit(String.valueOf(row.getCell(4).getNumericCellValue()));
                    }
                }

                if(row.getCell(5)==null || row.getCell(5).getCellType().equals(CellType.BLANK)){
                    throw new BeadminException("第"+row.getRowNum()+"行【位置号】字段不能为空");
                }else {
                    if (row.getCell(5).getCellType() == CellType.STRING)
                        productBomOverview.setPositionNo(row.getCell(5).getStringCellValue());
                    else if (row.getCell(5).getCellType() == CellType.NUMERIC) {
                        productBomOverview.setPositionNo(String.valueOf(row.getCell(5).getNumericCellValue()));
                    } else if (row.getCell(5).getCellType() == CellType.FORMULA) {
                        productBomOverview.setPositionNo(String.valueOf(row.getCell(5).getCellFormula()));
                    }
                }



                    if (row.getCell(6) != null) {
                        if (row.getCell(6).getCellType() == CellType.STRING)
                            productBomOverview.setBrand(row.getCell(6).getStringCellValue());
                        else if (row.getCell(6).getCellType() == CellType.NUMERIC)
                            productBomOverview.setBrand(String.valueOf(row.getCell(6).getNumericCellValue()));
                    }
                    if (row.getCell(7) != null) {
                        if (row.getCell(7).getCellType() == CellType.STRING)
                            productBomOverview.setMaterialClassification(row.getCell(7).getStringCellValue());
                        else if (row.getCell(7).getCellType() == CellType.NUMERIC)
                            productBomOverview.setMaterialClassification(String.valueOf(row.getCell(7).getNumericCellValue()));
                    }
                    if (row.getCell(8) != null) {
                        if (row.getCell(8).getCellType() == CellType.STRING)
                            productBomOverview.setMaterialType(row.getCell(8).getStringCellValue());
                        else if (row.getCell(8).getCellType() == CellType.NUMERIC)
                            productBomOverview.setMaterialType(String.valueOf(row.getCell(8).getNumericCellValue()));
                    }
                    if (row.getCell(9) != null) {
                        if (row.getCell(9).getCellType() == CellType.STRING)
                            productBomOverview.setIsJia(row.getCell(9).getStringCellValue());
                        else if (row.getCell(9).getCellType() == CellType.NUMERIC)
                            productBomOverview.setIsJia(String.valueOf(row.getCell(9).getNumericCellValue()));
                    }


                productBomOverview.setVersion(1);
                productBomOverview.setVersionSecond(0);
                productBomOverview.setIsMax(1);
                productBomOverview.setUserId(userId);
                productBomOverview.setUserName(userName);
                //productBomOverviewRepository.save(productBomOverview);
                first.add(productBomOverview);
            }
            //相同合并
            List<ProductBomOverview> add = new ArrayList<>();
            boolean flag ;
            for (ProductBomOverview p:first) {
                flag=true;
                for (ProductBomOverview p2:add) {
                    if(p.getMaterialCode().equals(p2.getMaterialCode()) && p.getPositionNo().equals(p2.getPositionNo())){
                        p2.setMaterialQty(p2.getMaterialQty()+p.getMaterialQty());
                        flag=false;
                        break;
                    }
                }
                if(flag){
                    add.add(p);
                }
            }
            for (ProductBomOverview p:add) {
                productBomOverviewRepository.save(p);
            }
        }else{
            List<ProductBomOverview> newBom = new ArrayList<>();
            for (Row row : sheet) {
                // 跳过表头
                if (row.getRowNum() == 0) {
                    continue;
                }
                ProductBomOverview productBomOverview = new ProductBomOverview();
                //String materialCode = serialNumberService.getNextSnByCode("MaterialCode");

                //productBomOverview.setMaterialCode(materialCode);
                productBomOverview.setBomCode(bomCode);
                //            productBomOverview.setProductCode(batchProductById.getProductCode());
                productBomOverview.setProductCode(batchProductById.getProductCode());


                    //System.out.println(formatter.formatCellValue(row.getCell(0)));
                if(row.getCell(0)==null || row.getCell(0).getCellType().equals(CellType.BLANK)){
                    throw new BeadminException("第"+row.getRowNum()+"行【物料编码】字段不能为空");
                }else{
                    if (row.getCell(0).getCellType() == CellType.STRING)
                        productBomOverview.setMaterialCode(formatter.formatCellValue(row.getCell(0)));
                    else if (row.getCell(0).getCellType() == CellType.NUMERIC) {
                        productBomOverview.setMaterialCode(formatter.formatCellValue(row.getCell(0)));
                    }
                }

                if (row.getCell(1) != null) {
                    if (row.getCell(1).getCellType() == CellType.STRING)
                        productBomOverview.setMaterialName(row.getCell(1).getStringCellValue());
                    else if (row.getCell(1).getCellType() == CellType.NUMERIC)
                        productBomOverview.setMaterialName(String.valueOf(row.getCell(1).getNumericCellValue()));
                }
                if(row.getCell(2)==null || row.getCell(2).getCellType().equals(CellType.BLANK)){
                    throw new BeadminException("第"+row.getRowNum()+"行【物料规格】字段不能为空");
                }else {
                    if (row.getCell(2).getCellType() == CellType.STRING)
                        productBomOverview.setMaterialSpec(row.getCell(2).getStringCellValue());
                    else if (row.getCell(2).getCellType() == CellType.NUMERIC)
                        productBomOverview.setMaterialSpec(String.valueOf(row.getCell(2).getNumericCellValue()));
                }


                if(row.getCell(3)==null || row.getCell(3).getCellType().equals(CellType.BLANK)){
                    throw new BeadminException("第"+row.getRowNum()+"行【数量】字段不能为空");
                }else{
                    if (row.getCell(3).getCellType() == CellType.NUMERIC) {
                        if ((int) row.getCell(3).getNumericCellValue() < 0) {
                            throw new BeadminException("物料数量不能为负！");
                        }
                        productBomOverview.setMaterialQty(row.getCell(3).getNumericCellValue());
                    } else if (row.getCell(3).getCellType() == CellType.STRING) {
                        if ((int) row.getCell(3).getNumericCellValue() < 0) {
                            throw new BeadminException("物料数量不能为负！");
                        }
                        productBomOverview.setMaterialQty(Double.parseDouble(row.getCell(3).getStringCellValue()));
                    }
                }

                if(row.getCell(4)==null || row.getCell(4).getCellType().equals(CellType.BLANK)){
                    throw new BeadminException("第"+row.getRowNum()+"行【单位】字段不能为空");
                }else{
                    if (row.getCell(4).getCellType() == CellType.STRING)
                        productBomOverview.setUnit(row.getCell(4).getStringCellValue());
                    else if (row.getCell(4).getCellType() == CellType.NUMERIC) {
                        productBomOverview.setUnit(String.valueOf(row.getCell(4).getNumericCellValue()));
                    }
                }

                if(row.getCell(5)==null || row.getCell(5).getCellType().equals(CellType.BLANK)){
                    throw new BeadminException("第"+row.getRowNum()+"行【位置号】字段不能为空");
                }else {
                    if (row.getCell(5).getCellType() == CellType.STRING)
                        productBomOverview.setPositionNo(row.getCell(5).getStringCellValue());
                    else if (row.getCell(5).getCellType() == CellType.NUMERIC) {
                        productBomOverview.setPositionNo(String.valueOf(row.getCell(5).getNumericCellValue()));
                    } else if (row.getCell(5).getCellType() == CellType.FORMULA) {
                        productBomOverview.setPositionNo(String.valueOf(row.getCell(5).getCellFormula()));
                    }
                }



                if (row.getCell(6) != null) {
                    if (row.getCell(6).getCellType() == CellType.STRING)
                        productBomOverview.setBrand(row.getCell(6).getStringCellValue());
                    else if (row.getCell(6).getCellType() == CellType.NUMERIC)
                        productBomOverview.setBrand(String.valueOf(row.getCell(6).getNumericCellValue()));
                }
                if (row.getCell(7) != null) {
                    if (row.getCell(7).getCellType() == CellType.STRING)
                        productBomOverview.setMaterialClassification(row.getCell(7).getStringCellValue());
                    else if (row.getCell(7).getCellType() == CellType.NUMERIC)
                        productBomOverview.setMaterialClassification(String.valueOf(row.getCell(7).getNumericCellValue()));
                }
                if (row.getCell(8) != null) {
                    if (row.getCell(8).getCellType() == CellType.STRING)
                        productBomOverview.setMaterialType(row.getCell(8).getStringCellValue());
                    else if (row.getCell(8).getCellType() == CellType.NUMERIC)
                        productBomOverview.setMaterialType(String.valueOf(row.getCell(8).getNumericCellValue()));
                }
                if (row.getCell(9) != null) {
                    if (row.getCell(9).getCellType() == CellType.STRING)
                        productBomOverview.setIsJia(row.getCell(9).getStringCellValue());
                    else if (row.getCell(9).getCellType() == CellType.NUMERIC)
                        productBomOverview.setIsJia(String.valueOf(row.getCell(9).getNumericCellValue()));
                }


                productBomOverview.setVersion(1);
                productBomOverview.setIsMax(1);
                productBomOverview.setUserId(userId);
                productBomOverview.setUserName(userName);
                newBom.add(productBomOverview);
            }
            //将old和new去重合并
            List<ProductBomOverview> temp = new ArrayList<>();
            Boolean flag = false;
            for (ProductBomOverview np:newBom) {
                String mCode = np.getMaterialCode();
                String position = np.getPositionNo();
                for (ProductBomOverview op: oldBom) {
                    //如果物料号以及位置号一样，数量合并
                    if(op.getMaterialCode().equals(mCode) && op.getPositionNo().equals(position)){
                        op.setMaterialQty(op.getMaterialQty()+np.getMaterialQty());
                        flag = false;
                        break;
                    }
                    flag = true;
                }
                if(flag){
                    temp.add(np);
                    flag=false;
                }
            }

            //删除所有，并添加oldBom
            productBomOverviewRepository.deleteProductBomOverviewsByProductCodeAndDelete(batchProductById.getProductCode(),0);
            productBomOverviewRepository.flush();
            for (ProductBomOverview p: temp) {
                oldBom.add(p);
            }
            for (ProductBomOverview p:oldBom) {
                productBomOverviewRepository.save(p);
            }
        }
         **/



    @Transactional
    @Override
    public void addStandBomByTemplate(String standTypeCode,String standBomCode,int version, MultipartFile file) throws IOException {
        InputStream inputStream = file.getInputStream();
        //这里判断一下excel是xls还是xlsx
        String filename = file.getOriginalFilename();
        String suffix = filename.substring(filename.lastIndexOf(".")+1);
        Workbook workbook;
        if(suffix.equals("xls")){
            workbook = new HSSFWorkbook(inputStream);
        }else{
            workbook = new XSSFWorkbook(inputStream);
        }

        Sheet sheet = workbook.getSheetAt(0);
        DataFormatter formatter = new DataFormatter();
        // 遍历行并将数据保存到数据库中

        for (Row row : sheet) {
            // 跳过表头
            if (row.getRowNum() == 0) {
                continue;
            }

             ProductBomOverview productBomOverview = new ProductBomOverview();

             productBomOverview.setBomCode(standBomCode);
             productBomOverview.setProductCode(standTypeCode);
             productBomOverview.setVersion(version);


            if (row.getCell(0).getCellType() == CellType.STRING)
                productBomOverview.setMaterialCode(formatter.formatCellValue(row.getCell(0)));
            else if (row.getCell(0).getCellType() == CellType.NUMERIC) {
                productBomOverview.setMaterialCode(formatter.formatCellValue(row.getCell(0)));
            } else
                throw new BeadminException("第"+row.getRowNum()+"行【物料编码】字段不能为空");

            if (row.getCell(1) != null) {
                if (row.getCell(1).getCellType() == CellType.STRING)
                    productBomOverview.setMaterialName(row.getCell(1).getStringCellValue());
                else if (row.getCell(1).getCellType() == CellType.NUMERIC)
                    productBomOverview.setMaterialName(String.valueOf(row.getCell(1).getNumericCellValue()));
            }

            if (row.getCell(2).getCellType() == CellType.STRING)
                productBomOverview.setMaterialSpec(row.getCell(2).getStringCellValue());
            else if (row.getCell(2).getCellType() == CellType.NUMERIC)
                productBomOverview.setMaterialSpec(String.valueOf(row.getCell(2).getNumericCellValue()));
            else
                throw new BeadminException("第"+row.getRowNum()+"行【物料规格】字段不能为空");

            if (row.getCell(3).getCellType() == CellType.NUMERIC) {
                if( row.getCell(3).getNumericCellValue()<0){
                    throw new BeadminException("物料数量不能为负！");
                }
                productBomOverview.setMaterialQty(row.getCell(3).getNumericCellValue());
            } else if (row.getCell(3).getCellType() == CellType.STRING) {
                if( row.getCell(3).getNumericCellValue()<0){
                    throw new BeadminException("物料数量不能为负！");
                }
                productBomOverview.setMaterialQty(Double.parseDouble(row.getCell(3).getStringCellValue()));
            } else
                throw new BeadminException("第"+row.getRowNum()+"行【数量】字段不能为空");

            if (row.getCell(4).getCellType() == CellType.STRING)
                productBomOverview.setUnit(row.getCell(4).getStringCellValue());
            else if (row.getCell(4).getCellType() == CellType.NUMERIC) {
                productBomOverview.setUnit(String.valueOf(row.getCell(4).getNumericCellValue()));
            } else
                throw new BeadminException("第"+row.getRowNum()+"行【单位】字段不能为空");

            if (row.getCell(5).getCellType() == CellType.STRING)
                productBomOverview.setPositionNo(row.getCell(5).getStringCellValue());
            else if (row.getCell(5).getCellType() == CellType.NUMERIC) {
                productBomOverview.setPositionNo(String.valueOf(row.getCell(5).getNumericCellValue()));
            } else if (row.getCell(5).getCellType() == CellType.FORMULA) {
                productBomOverview.setPositionNo(String.valueOf(row.getCell(5).getCellFormula()));
            } else
                throw new BeadminException("第"+row.getRowNum()+"行【位置号】字段不能为空");

            try {
                if(row.getCell(6)!=null) {
                    if (row.getCell(6).getCellType() == CellType.STRING)
                        productBomOverview.setBrand(row.getCell(6).getStringCellValue());
                    else if (row.getCell(6).getCellType() == CellType.NUMERIC)
                        productBomOverview.setBrand(String.valueOf(row.getCell(6).getNumericCellValue()));
                }
                if(row.getCell(7)!=null) {
                    if (row.getCell(7).getCellType() == CellType.STRING)
                        productBomOverview.setMaterialClassification(row.getCell(7).getStringCellValue());
                    else if (row.getCell(7).getCellType() == CellType.NUMERIC)
                        productBomOverview.setMaterialClassification(String.valueOf(row.getCell(7).getNumericCellValue()));
                }
                if(row.getCell(8)!=null) {
                    if (row.getCell(8).getCellType() == CellType.STRING)
                        productBomOverview.setMaterialType(row.getCell(8).getStringCellValue());
                    else if (row.getCell(8).getCellType() == CellType.NUMERIC)
                        productBomOverview.setMaterialType(String.valueOf(row.getCell(8).getNumericCellValue()));
                }
                if(row.getCell(9)!=null) {
                    if (row.getCell(9).getCellType() == CellType.STRING)
                        productBomOverview.setIsJia(row.getCell(9).getStringCellValue());
                    else if (row.getCell(9).getCellType() == CellType.NUMERIC)
                        productBomOverview.setIsJia(String.valueOf(row.getCell(9).getNumericCellValue()));
                }
            }catch (Exception e){
                if(e.getMessage().startsWith("第")||e.getMessage().startsWith("物")){
                    throw new RuntimeException(e.getMessage());
                }else {
                    throw new RuntimeException("请使用正确模板！");
                }
            }

            String materialCode = productBomOverview.getMaterialCode();
            String positionNo = productBomOverview.getPositionNo();
            List<ProductBomOverview> productBomOverviewList = productBomOverviewRepository.findProductBomOverviewsByProductCodeAndBomCodeAndVersionAndMaterialCodeAndPositionNoAndDelete(standTypeCode,standBomCode,version,materialCode,positionNo,0);
            if(productBomOverviewList.size()==0){
                productBomOverviewRepository.save(productBomOverview);
            }else{

                ProductBomOverview productBomOverview1 = productBomOverviewList.get(0);

                if(!productBomOverview1.getMaterialName().equals(productBomOverview.getMaterialName())) throw new BeadminException("物料号："+materialCode+"，位置号:"+positionNo+"; 物料名称不一致");
                if(!productBomOverview1.getMaterialSpec().equals(productBomOverview.getMaterialSpec())) throw new BeadminException("物料号："+materialCode+"，位置号:"+positionNo+"; 规格不一致");
                if(!productBomOverview1.getUnit().equals(productBomOverview.getUnit())) throw new BeadminException("物料号："+materialCode+"，位置号:"+positionNo+"; 单位不一致");
                if(!productBomOverview1.getBrand().equals(productBomOverview.getBrand())) throw new BeadminException("物料号："+materialCode+"，位置号:"+positionNo+"; 品牌不一致");
                if(!productBomOverview1.getMaterialClassification().equals(productBomOverview.getMaterialClassification())) throw new BeadminException("物料号："+materialCode+"，位置号:"+positionNo+"; 物料分类不一致");
                if(!productBomOverview1.getMaterialType().equals(productBomOverview.getMaterialType())) throw new BeadminException("物料号："+materialCode+"，位置号:"+positionNo+"; 物料种类不一致");
                if(!productBomOverview1.getIsJia().equals(productBomOverview.getIsJia())) throw new BeadminException("物料号："+materialCode+"，位置号:"+positionNo+"; 是否甲供料不一致");


                double materialQty = productBomOverview1.getMaterialQty();
                double cnt = productBomOverview.getMaterialQty();
                productBomOverview1.setMaterialQty(cnt+materialQty);
                productBomOverviewRepository.save(productBomOverview1);
            }

            MaterialInfo materialInfo =materialInfoRepository.findMaterialInfoByMaterialCodeAndDelete(materialCode,0);
            if(materialInfo==null){
                MaterialInfo materialInfo1 =new MaterialInfo();
                materialInfo1.setMaterialCode(materialCode);
                materialInfo1.setMaterialName(productBomOverview.getMaterialName());
                materialInfo1.setMaterialSpec(productBomOverview.getMaterialSpec());
//                materialInfo1.setPara();
                materialInfo1.setUnit(productBomOverview.getUnit());
                materialInfo1.setCategory(productBomOverview.getMaterialClassification());
                //                materialInfo1.setBarCode();
                materialInfo1.setBrand(productBomOverview.getBrand());
                materialInfo1.setEffective(1);
                materialInfo1.setMaterialProperty("生产加工");
                materialInfoRepository.save(materialInfo1);
            }


        }
    }


    @Override
    public boolean checkTemplate(MultipartFile file) throws IOException {

        InputStream inputStream = file.getInputStream();
        //这里判断一下excel是xls还是xlsx
        String filename = file.getOriginalFilename();
        String suffix = filename.substring(filename.lastIndexOf(".")+1);
        Workbook workbook;
        if(suffix.equals("xls")){
            workbook = new HSSFWorkbook(inputStream);
        }else{
            workbook = new XSSFWorkbook(inputStream);
        }

        Sheet sheet = workbook.getSheetAt(0);
        DataFormatter formatter = new DataFormatter();
        // 遍历行并将数据保存到数据库中

        for (Row row : sheet) {
            // 跳过表头
            if (row.getRowNum() == 0) {
                continue;
            }
            ProductBomOverview productBomOverview = new ProductBomOverview();
            //String materialCode = serialNumberService.getNextSnByCode("MaterialCode");

            //productBomOverview.setMaterialCode(materialCode);
           // productBomOverview.setBomCode(bomCode);
            //            productBomOverview.setProductCode(batchProductById.getProductCode());
           // productBomOverview.setProductCode(batchProductById.getProductCode());


            if (row.getCell(0).getCellType() == CellType.STRING)
                productBomOverview.setMaterialCode(row.getCell(0).getStringCellValue());
            else if (row.getCell(0).getCellType() == CellType.NUMERIC) {
                row.getCell(0).setCellType(CellType.STRING);
                productBomOverview.setMaterialCode(row.getCell(0).getStringCellValue());
            }
            else
                throw new BeadminException("文件中没有第0列【物料编码】,请按照模板添加物料");
            if(row.getCell(1) != null){
                if (row.getCell(1).getCellType() == CellType.STRING)
                    productBomOverview.setMaterialName(row.getCell(1).getStringCellValue());
                else if (row.getCell(1).getCellType() == CellType.NUMERIC)
                    productBomOverview.setMaterialName(String.valueOf(row.getCell(1).getNumericCellValue()));
            }

            if (row.getCell(2).getCellType() == CellType.STRING)
                productBomOverview.setMaterialSpec(row.getCell(2).getStringCellValue());
            else if (row.getCell(2).getCellType() == CellType.NUMERIC)
                productBomOverview.setMaterialSpec(String.valueOf(row.getCell(2).getNumericCellValue()));
            else
                throw new BeadminException("文件中没有第2列【物料规格】,请按照模板添加物料");

            if (row.getCell(3).getCellType() == CellType.NUMERIC)
                productBomOverview.setMaterialQty((int) row.getCell(3).getNumericCellValue());
            else if (row.getCell(3).getCellType() == CellType.STRING) {
                productBomOverview.setMaterialQty(Integer.parseInt(row.getCell(3).getStringCellValue()));
            } else
                throw new BeadminException("文件中没有第3列【数量】,请按照模板添加物料");

            if (row.getCell(4).getCellType() == CellType.STRING)
                productBomOverview.setUnit(row.getCell(4).getStringCellValue());
            else if (row.getCell(4).getCellType() == CellType.NUMERIC) {
                productBomOverview.setUnit(String.valueOf(row.getCell(4).getNumericCellValue()));
            } else
                throw new BeadminException("文件中没有第4列【单位】,请按照模板添加物料");

            if (row.getCell(5).getCellType() == CellType.STRING)
                productBomOverview.setPositionNo(row.getCell(5).getStringCellValue());
            else if (row.getCell(5).getCellType() == CellType.NUMERIC) {
                productBomOverview.setPositionNo(String.valueOf(row.getCell(5).getNumericCellValue()));
            } else if(row.getCell(5).getCellType() == CellType.FORMULA){
                productBomOverview.setPositionNo(String.valueOf(row.getCell(5).getCellFormula()));
            }else
                throw new BeadminException("文件中没有第5列【位置号】,请按照模板添加物料");

            try {
                if(row.getCell(6)!=null) {
                    if (row.getCell(6).getCellType() == CellType.STRING)
                        productBomOverview.setBrand(row.getCell(6).getStringCellValue());
                    else if (row.getCell(6).getCellType() == CellType.NUMERIC)
                        productBomOverview.setBrand(String.valueOf(row.getCell(6).getNumericCellValue()));
                }
                if(row.getCell(7)!=null) {
                    if (row.getCell(7).getCellType() == CellType.STRING)
                        productBomOverview.setMaterialClassification(row.getCell(7).getStringCellValue());
                    else if (row.getCell(7).getCellType() == CellType.NUMERIC)
                        productBomOverview.setMaterialClassification(String.valueOf(row.getCell(7).getNumericCellValue()));
                }
                if(row.getCell(8)!=null) {
                    if (row.getCell(8).getCellType() == CellType.STRING)
                        productBomOverview.setMaterialType(row.getCell(8).getStringCellValue());
                    else if (row.getCell(8).getCellType() == CellType.NUMERIC)
                        productBomOverview.setMaterialType(String.valueOf(row.getCell(8).getNumericCellValue()));
                }
                if(row.getCell(9)!=null) {
                    if (row.getCell(9).getCellType() == CellType.STRING)
                        productBomOverview.setIsJia(row.getCell(9).getStringCellValue());
                    else if (row.getCell(9).getCellType() == CellType.NUMERIC)
                        productBomOverview.setIsJia(String.valueOf(row.getCell(9).getNumericCellValue()));
                }
            }catch (Exception e){
                throw new RuntimeException("请使用正确模板！");
            }


            //productBomOverviewRepository.save(productBomOverview);

        }
        return true;

    }

    /**
     * 解析BOM
     * @param b
     * @param standFileInfo
     */
    @Override
    public void analyzeBom(BatchProduct b , StandFileInfo standFileInfo) throws IOException {
        String fileName = standFileInfo.getFileCode() + "+" + standFileInfo.getVersion() + "+" + standFileInfo.getFileName();
        InputStream inputStream = minioService.downloadOne(standFileInfo.getBucketName(), fileName);

        String bomCode = serialNumberService.getNextSnByCode(b.getInfoId() + "_B_Code");
        if (bomCode == null) {
            bomCode = serialNumberService.getNextSnByCode("BomCode");
        }

        String projectCode = b.getProductCode().substring(0,b.getProductCode().indexOf("."));
        bomCode = projectCode+"."+"002."+bomCode;

        b.setBomId(standFileInfo.getId());
        b.setBomTime(new Date());
        if(b.getReviewState().equals(2)){
            b.setReviewState(0);
        }
        batchProductRepository.save(b);

        //这里判断一下excel是xls还是xlsx
        String suffix = fileName.substring(fileName.lastIndexOf(".")+1);
        Workbook workbook;
        if(suffix.equals("xls")){
            workbook = new HSSFWorkbook(inputStream);
        }else{
            workbook = new XSSFWorkbook(inputStream);
        }
        Sheet sheet = workbook.getSheetAt(0);
        DataFormatter formatter = new DataFormatter();



        // 遍历行并将数据保存到数据库中
        for (Row row : sheet) {
            // 跳过表头
            if (row.getRowNum() == 0) {
                continue;
            }
            ProductBomOverview productBomOverview = new ProductBomOverview();
            //String materialCode = serialNumberService.getNextSnByCode("MaterialCode");

            //productBomOverview.setMaterialCode(materialCode);
            // productBomOverview.setBomCode(bomCode);
            //            productBomOverview.setProductCode(batchProductById.getProductCode());
            // productBomOverview.setProductCode(batchProductById.getProductCode());


            if (row.getCell(0).getCellType() == CellType.STRING)
                productBomOverview.setMaterialCode(row.getCell(0).getStringCellValue());
            else if (row.getCell(0).getCellType() == CellType.NUMERIC) {
                row.getCell(0).setCellType(CellType.STRING);
                productBomOverview.setMaterialCode(row.getCell(0).getStringCellValue());
            }
            else
                throw new BeadminException("文件中没有第0列【物料编码】,请按照模板添加物料");
            if(row.getCell(1) != null){
                if (row.getCell(1).getCellType() == CellType.STRING)
                    productBomOverview.setMaterialName(row.getCell(1).getStringCellValue());
                else if (row.getCell(1).getCellType() == CellType.NUMERIC)
                    productBomOverview.setMaterialName(String.valueOf(row.getCell(1).getNumericCellValue()));
            }

            if (row.getCell(2).getCellType() == CellType.STRING)
                productBomOverview.setMaterialSpec(row.getCell(2).getStringCellValue());
            else if (row.getCell(2).getCellType() == CellType.NUMERIC)
                productBomOverview.setMaterialSpec(String.valueOf(row.getCell(2).getNumericCellValue()));
            else
                throw new BeadminException("文件中没有第2列【物料规格】,请按照模板添加物料");

            if (row.getCell(3).getCellType() == CellType.NUMERIC)
                productBomOverview.setMaterialQty((int) row.getCell(3).getNumericCellValue());
            else if (row.getCell(3).getCellType() == CellType.STRING) {
                productBomOverview.setMaterialQty(Integer.parseInt(row.getCell(3).getStringCellValue()));
            } else
                throw new BeadminException("文件中没有第3列【数量】,请按照模板添加物料");

            if (row.getCell(4).getCellType() == CellType.STRING)
                productBomOverview.setUnit(row.getCell(4).getStringCellValue());
            else if (row.getCell(4).getCellType() == CellType.NUMERIC) {
                productBomOverview.setUnit(String.valueOf(row.getCell(4).getNumericCellValue()));
            } else
                throw new BeadminException("文件中没有第4列【单位】,请按照模板添加物料");

            if (row.getCell(5).getCellType() == CellType.STRING)
                productBomOverview.setPositionNo(row.getCell(5).getStringCellValue());
            else if (row.getCell(5).getCellType() == CellType.NUMERIC) {
                productBomOverview.setPositionNo(String.valueOf(row.getCell(5).getNumericCellValue()));
            } else if(row.getCell(5).getCellType() == CellType.FORMULA){
                productBomOverview.setPositionNo(String.valueOf(row.getCell(5).getCellFormula()));
            }else
                throw new BeadminException("文件中没有第5列【位置号】,请按照模板添加物料");

            try {
                if(row.getCell(6)!=null) {
                    if (row.getCell(6).getCellType() == CellType.STRING)
                        productBomOverview.setBrand(row.getCell(6).getStringCellValue());
                    else if (row.getCell(6).getCellType() == CellType.NUMERIC)
                        productBomOverview.setBrand(String.valueOf(row.getCell(6).getNumericCellValue()));
                }
                if(row.getCell(7)!=null) {
                    if (row.getCell(7).getCellType() == CellType.STRING)
                        productBomOverview.setMaterialClassification(row.getCell(7).getStringCellValue());
                    else if (row.getCell(7).getCellType() == CellType.NUMERIC)
                        productBomOverview.setMaterialClassification(String.valueOf(row.getCell(7).getNumericCellValue()));
                }
                if(row.getCell(8)!=null) {
                    if (row.getCell(8).getCellType() == CellType.STRING)
                        productBomOverview.setMaterialType(row.getCell(8).getStringCellValue());
                    else if (row.getCell(8).getCellType() == CellType.NUMERIC)
                        productBomOverview.setMaterialType(String.valueOf(row.getCell(8).getNumericCellValue()));
                }
                if(row.getCell(9)!=null) {
                    if (row.getCell(9).getCellType() == CellType.STRING)
                        productBomOverview.setIsJia(row.getCell(9).getStringCellValue());
                    else if (row.getCell(9).getCellType() == CellType.NUMERIC)
                        productBomOverview.setIsJia(String.valueOf(row.getCell(9).getNumericCellValue()));
                }
            }catch (Exception e){
                throw new RuntimeException(e.getMessage());
            }
            //productBomOverviewRepository.save(productBomOverview);
            productBomOverview.setVersion(1);
            productBomOverview.setBomCode(bomCode);
            productBomOverview.setIsMax(1);
            productBomOverview.setProductCode(b.getProductCode());
            productBomOverviewRepository.save(productBomOverview);
        }
    }

    @Transactional
    @Override
    public void analyzeStandBom(BatchProduct b, StandFileInfo standFileInfo) {
        String fileName = standFileInfo.getFileCode() + "+" + standFileInfo.getVersion() + "+" + standFileInfo.getFileName();
        InputStream inputStream = minioService.downloadOne(standFileInfo.getBucketName(), fileName);

        String bomCode = serialNumberService.getNextSnByCode(b.getInfoId() + "_B_Code");
        if (bomCode == null) {
            bomCode = serialNumberService.getNextSnByCode("BomCode");
        }

        String projectCode = b.getProductCode().substring(0,b.getProductCode().indexOf("."));
        bomCode = projectCode+"."+"002."+bomCode;

        b.setBomId(standFileInfo.getId());
        b.setBomTime(new Date());
        if(b.getReviewState().equals(2)){
            b.setReviewState(0);
        }
        batchProductRepository.save(b);

        String productCode = standFileInfo.getStandTypeCode();
        String standBomCode = standFileInfo.getFileCode();
        int version  = standFileInfo.getVersion();
        List<ProductBomOverview> productBomOverviewList = productBomOverviewRepository.findProductBomOverviewsByProductCodeAndBomCodeAndVersionAndDelete(productCode,standBomCode,version,0);

        for (ProductBomOverview productBomOverview1:productBomOverviewList){
            ProductBomOverview productBomOverview = new ProductBomOverview();
            productBomOverview.setMaterialCode(productBomOverview1.getMaterialCode());
            productBomOverview.setMaterialName(productBomOverview1.getMaterialName());
            productBomOverview.setMaterialSpec(productBomOverview1.getMaterialSpec());
            productBomOverview.setMaterialQty(productBomOverview1.getMaterialQty());
            productBomOverview.setUnit(productBomOverview1.getUnit());
            productBomOverview.setPositionNo(productBomOverview1.getPositionNo());
            productBomOverview.setBrand(productBomOverview1.getBrand());
            productBomOverview.setMaterialClassification(productBomOverview1.getMaterialClassification());
            productBomOverview.setMaterialType(productBomOverview1.getMaterialType());
            productBomOverview.setIsJia(productBomOverview1.getIsJia());

            productBomOverview.setVersion(version);
            productBomOverview.setBomCode(bomCode);
            productBomOverview.setIsMax(1);
            productBomOverview.setProductCode(b.getProductCode());
            productBomOverviewRepository.save(productBomOverview);


        }
    }




    public Page<ProductBomOverviewEx> getProductBomOverviewByBatchProductId(HttpServletRequest request,PageEntity pageEntity, String batchProductId){
        BatchProduct batchProductById = batchProductRepository.findBatchProductById(batchProductId);
        String engineer = batchProductById.getEngineer();
        UserEx userEx = userService.viewBaseInfo(request);
        String userName = userEx.getName();
        if(!(userName.equals(engineer) || userName.equals("admin"))){
            throw new RuntimeException("当前用户无权限");
        }

        Pageable pageable = PageRequest.of(
                pageEntity.getCurrent() -1,
                pageEntity.getPageSize());
        Page<ProductBomOverview> page = productBomOverviewRepository.findAll((root, criteriaQuery, criteriaBuilder) -> {
                    List<Predicate> list = new ArrayList<>();
                    Predicate pre1 =   JpaQueryBuilder.getPredicate(root, pageEntity.getFilters(), criteriaBuilder);
                    list.add(pre1);
                    Predicate pre2 = criteriaBuilder.equal(root.get(ProductBomOverview_.delete), 0); //过滤为0
                    list.add(pre2);

                    Predicate[] p = new Predicate[list.size()];
                    List<Order> orders = new ArrayList<>();
                    if(pageEntity.getSorters() != null && pageEntity.getSorters().size() > 0) {
                        orders = JpaSortBuilder.getPredicate(root, pageEntity.getSorters(), criteriaBuilder);
                        criteriaQuery.orderBy(orders);
                    } else {
                        criteriaQuery.orderBy(criteriaBuilder.desc(root.get(ProductBomOverview_.createTime).as(Date.class)));
                    }
                    return  criteriaBuilder.and(list.toArray(p));
                }
                , pageable);
        Page<ProductBomOverviewEx> page1 = page.map(productBomOverviewExMapper::toEntity);
        return page1;
    }

    @Override
    public Page<ProductBomOverviewEx> getProductBomOverviewOfStandProduct( PageEntity pageEntity, String standTypeId) {
        StandFileInfo standFileInfo = standFileInfoRepository.findStandFileInfoByStandTypeIdAndCategoryAndStatusAndDelete(standTypeId,"BOM",1,0);
        String standBomCode;
        int version=0;
        if(standFileInfo==null) {
            standBomCode = null;
            version = 0;
        }else{
            standBomCode=standFileInfo.getFileCode();
            version = standFileInfo.getVersion();
        }
        Pageable pageable = PageRequest.of(
                pageEntity.getCurrent() -1,
                pageEntity.getPageSize());
        int finalVersion = version;
        Page<ProductBomOverview> page = productBomOverviewRepository.findAll((root, criteriaQuery, criteriaBuilder) -> {
                    List<Predicate> list = new ArrayList<>();
                    Predicate pre1 =   JpaQueryBuilderNew.getPredicate(root, pageEntity.getFilters(), criteriaBuilder);
                    list.add(pre1);
                    Predicate pre2 = criteriaBuilder.equal(root.get(ProductBomOverview_.bomCode), standBomCode); //过滤为0
                    list.add(pre2);
                    Predicate pre3 = criteriaBuilder.equal(root.get(ProductBomOverview_.version), finalVersion); //过滤为0
                    list.add(pre3);
                    Predicate pre4 = criteriaBuilder.equal(root.get(ProductBomOverview_.delete), 0); //过滤为0
                    list.add(pre4);

                    Predicate[] p = new Predicate[list.size()];
                    List<Order> orders = new ArrayList<>();
                    if(pageEntity.getSorters() != null && pageEntity.getSorters().size() > 0) {
                        orders = JpaSortBuilder.getPredicate(root, pageEntity.getSorters(), criteriaBuilder);
                        criteriaQuery.orderBy(orders);
                    } else {
                        criteriaQuery.orderBy(criteriaBuilder.desc(root.get(ProductBomOverview_.createTime).as(Date.class)));
                    }
                    return  criteriaBuilder.and(list.toArray(p));
                }
                , pageable);
        Page<ProductBomOverviewEx> page1 = page.map(productBomOverviewExMapper::toEntity);
        return page1;
    }


    public void confirmUpdate(List<ProductBomOverviewDto> ProductBomOverviewDtos){
        String productCode = ProductBomOverviewDtos.get(0).getProductCode();

        BatchProduct product = batchProductRepository.findBatchProductByProductCodeAndDelete(productCode, 0);
        product.setBomIsChange(1);
        batchProductRepository.save(product);
        //去重  保留版本最大的
        //导出的时候也可用以下代码
        Map<String, Integer> hm = new HashMap<>();
        List<ProductBomOverviewDto> newProductBomOverviewDtos = new ArrayList<>();
        for (ProductBomOverviewDto p : ProductBomOverviewDtos) {
            String mCode = p.getMaterialCode();
            int version = p.getVersion();
            if(hm.containsKey(mCode)){
                int maxV = hm.get(mCode);
                if(version>maxV){
                    hm.put(mCode,version);
                    newProductBomOverviewDtos.removeIf(obj -> obj.getMaterialCode().equals(mCode));
                    newProductBomOverviewDtos.add(p);
                }
            }else{
                hm.put(mCode,version);
                newProductBomOverviewDtos.add(p);
            }
        }
        //判断数量是否变化
        for (ProductBomOverviewDto p:newProductBomOverviewDtos) {
            List<ProductBomOverview> pOlds = productBomOverviewRepository.findProductBomOverviewByProductCodeAndMaterialCodeAndDelete(p.getProductCode(), p.getMaterialCode(), 0);
            //获取数据库中，最新版本的物料数量
            int maxIndex=0;
            int maxV=0;
            for (int i =0;i<pOlds.size();i++) {
                if(pOlds.get(i).getVersion()>maxV) {
                    maxV = Math.max(maxV, pOlds.get(i).getVersion());
                    maxIndex = i;
                }
            }
            if(pOlds.get(maxIndex).getMaterialQty()==p.getMaterialQty()){
                continue;
            }else{
                pOlds.get(maxIndex).setIsMax(0);
                productBomOverviewRepository.save(pOlds.get(maxIndex));
            }
            ProductBomOverview tempP = new ProductBomOverview();
            tempP.setVersion(maxV+1);
            tempP.setMaterialQty(p.getMaterialQty());
            tempP.setIsMax(1);
            if(p.getProductCode()!=null)
                tempP.setProductCode(p.getProductCode());
            if(p.getUnit()!=null)
                tempP.setUnit(p.getUnit());
            if(p.getBomCode()!=null)
                tempP.setBomCode(p.getBomCode());
            if(p.getBomSequence()!=null)
                tempP.setBomSequence(p.getBomSequence());
            if(p.getMaterialCode()!=null)
                tempP.setMaterialCode(p.getMaterialCode());
            if(p.getMaterialName()!=null)
                tempP.setMaterialName(p.getMaterialName());
            if(p.getMaterialClassification()!=null)
                tempP.setMaterialClassification(p.getMaterialClassification());
            if(p.getMaterialType()!=null)
                tempP.setMaterialType(p.getMaterialType());
            if(p.getMaterialSpec()!=null)
                tempP.setMaterialSpec(p.getMaterialSpec());
            if(p.getPartCode()!=null)
                tempP.setPartCode(p.getPartCode());
//            if(p.getChangeStatus()!=null)
                tempP.setChangeStatus(1);
            if(p.getPositionNo()!=null)
                tempP.setPositionNo(p.getPositionNo());
            if(p.getBrand()!=null)
                tempP.setBrand(p.getBrand());
            if(p.getArchiveFlag()!=null)
                tempP.setArchiveFlag(p.getArchiveFlag());
            if(p.getTenantId()!=null)
                tempP.setTenantId(p.getTenantId());
            if(p.getIsolation()!=null)
                tempP.setIsolation(p.getIsolation());
            productBomOverviewRepository.save(tempP);
        }
    }

    public void change(List<String> ids,List<Integer> nums) throws ParseException {
        for (int i=0;i<ids.size();i++) {
            String id = ids.get(i);
            int integer = nums.get(i);
            ProductBomOverview b = productBomOverviewRepository.findProductBomOverviewById(id);
            BatchProduct p = batchProductRepository.findBatchProductByProductCodeAndDelete(b.getProductCode(), 0);
            BomReview bomReview = new BomReview();
            bomReview.setBomBeforeId(id);
            bomReview.setBomCode(b.getBomCode());
            bomReview.setBomSequence(b.getBomSequence());
            bomReview.setIsJia(b.getIsJia());
            bomReview.setBrand(b.getBrand());
            //物料数量修改
            bomReview.setMaterialQty(integer);
            bomReview.setMaterialName(b.getMaterialName());
            bomReview.setMaterialCode(b.getMaterialCode());
            bomReview.setMaterialType(b.getMaterialType());
            bomReview.setMaterialSpec(b.getMaterialSpec());
            bomReview.setMaterialClassification(b.getMaterialClassification());

            bomReview.setVersion(b.getVersion());
            bomReview.setUserId(b.getUserId());
            bomReview.setUserName(b.getUserName());
            bomReview.setUnit(b.getUnit());
            bomReview.setPositionNo(b.getPositionNo());
            bomReview.setProjectName(p.getProjectName());
            bomReview.setProductCode(b.getProductCode());
            bomReviewRepository.save(bomReview);

            // 消息通知： 工程师->技术经理
            // 消息通知
            //-----------------------------------------------------------------------
            // 申请人 发送 工时补录 待办  审批人
            NotifyInfo notifyInfo =new NotifyInfo();
            notifyInfo.setModuleName("设计管理");
            notifyInfo.setFuncName("BOM变更");
            notifyInfo.setNotifyDesc("非标柜体编码为["+p.getProductCode()+"]--BOM更新,待审批");
            notifyInfo.setNotifyType("待办");
            if (p.getEngineer()==null){
                notifyInfo.setSenderName("-");
            }else{
                notifyInfo.setSenderName(p.getEngineer());
            }
            SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH-mm");
            Date date = new Date();
            notifyInfo.setSendTime(df.parse(df.format(date)));

            // 工时审核没有 用户id字段，用的用户名
            notifyInfo = notifyInfoRepository.save(notifyInfo);

            Info info = infoRepository.findInfoByIdAndDelete(p.getInfoId(), 0);
            TeamInfo teaminfo = teamInfoRepository.findTeamInfoByTeamIdAndCategoryAndDelete(info.getTeamId(), "技术经理", 0);

            if (teaminfo.getUserName() != null){
                notifyTemplateInfoService.sendNotify(notifyInfo,-2,teaminfo.getUserName(),teaminfo.getUserId());
            }

        }
    }


    @Autowired
    private BomReviewMapperEx bomReviewExMapper;

    @Override
    public Map<Object, Object> getProductBomOverviewAndReviewByPage(PageEntity pageEntity) {
//        List<SortExpressEntity> sorters = pageEntity.getSorters();
//        pageEntity.setSorters(null);

        Pageable pageable = PageRequest.of(
                pageEntity.getCurrent() -1,
                pageEntity.getPageSize());
        Page<ProductBomOverview> page = productBomOverviewRepository.findAll((root, criteriaQuery, criteriaBuilder) -> {
                    List<Predicate> list = new ArrayList<>();
                    Predicate pre1 =   JpaQueryBuilder.getPredicate(root, pageEntity.getFilters(), criteriaBuilder);
                    list.add(pre1);
                    Predicate pre2 = criteriaBuilder.equal(root.get(ProductBomOverview_.delete), 0); //过滤为0
                    list.add(pre2);
                    Predicate[] p = new Predicate[list.size()];
                    List<Order> orders = new ArrayList<>();
                    if(pageEntity.getSorters() != null && pageEntity.getSorters().size() > 0) {
                        orders = JpaSortBuilder.getPredicate(root, pageEntity.getSorters(), criteriaBuilder);
                        criteriaQuery.orderBy(orders);
                    } else {
//                        criteriaQuery.orderBy(criteriaBuilder.desc(root.get(ProductBomOverview_.createTime).as(Date.class)));
                        criteriaQuery.orderBy(criteriaBuilder.asc(root.get(ProductBomOverview_.bomSequence).as(String.class)));

                    }
                    return  criteriaBuilder.and(list.toArray(p));
                }
                , pageable);
        Page<ProductBomOverviewEx> page1 = page.map(productBomOverviewExMapper::toEntity);




        List<FilderExpressEntity> filters = pageEntity.getFilters();
        FilderExpressEntity temp = new FilderExpressEntity();
        temp.setAndOr("and");
        temp.setField("changeStatus");
        temp.setOperator("eq");
        temp.setValue((Integer)0);
        filters.add(temp);
        pageEntity.setFilters(filters);
//        pageEntity.setSorters(sorters);

        Pageable pageable2 = PageRequest.of(
                pageEntity.getCurrent() -1,
                pageEntity.getPageSize());
        Page<BomReview> pages = bomReviewRepository.findAll((root, criteriaQuery, criteriaBuilder) -> {
                    List<Predicate> list = new ArrayList<>();
                    Predicate pre1 =   JpaQueryBuilder.getPredicate(root, pageEntity.getFilters(), criteriaBuilder);
                    list.add(pre1);
                    Predicate pre2 = criteriaBuilder.equal(root.get(BomReview_.delete), 0); //过滤为0
                    list.add(pre2);
                    Predicate[] p = new Predicate[list.size()];
                    List<Order> orders = new ArrayList<>();
                    if(pageEntity.getSorters() != null && pageEntity.getSorters().size() > 0) {
                        orders = JpaSortBuilder.getPredicate(root, pageEntity.getSorters(), criteriaBuilder);
                        criteriaQuery.orderBy(orders);
                    } else {
                        criteriaQuery.orderBy(criteriaBuilder.desc(root.get(BomReview_.createTime).as(Date.class)));
                    }
                    return  criteriaBuilder.and(list.toArray(p));
                }
                , pageable2);
        Page<BomReviewEx> page2 = pages.map(bomReviewExMapper::toEntity);


        temp.setValue((Integer)3);
        filters.set(filters.size() - 1, temp);
        pageEntity.setFilters(filters);
        Pageable pageable3 = PageRequest.of(
                pageEntity.getCurrent() -1,
                pageEntity.getPageSize());
        Page<BomReview> pages2 = bomReviewRepository.findAll((root, criteriaQuery, criteriaBuilder) -> {
                    List<Predicate> list = new ArrayList<>();
                    Predicate pre1 =   JpaQueryBuilder.getPredicate(root, pageEntity.getFilters(), criteriaBuilder);
                    list.add(pre1);
                    Predicate pre2 = criteriaBuilder.equal(root.get(BomReview_.delete), 0); //过滤为0
                    list.add(pre2);
                    Predicate[] p = new Predicate[list.size()];
                    List<Order> orders = new ArrayList<>();
                    if(pageEntity.getSorters() != null && pageEntity.getSorters().size() > 0) {
                        orders = JpaSortBuilder.getPredicate(root, pageEntity.getSorters(), criteriaBuilder);
                        criteriaQuery.orderBy(orders);
                    } else {
                        criteriaQuery.orderBy(criteriaBuilder.desc(root.get(BomReview_.createTime).as(Date.class)));
                    }
                    return  criteriaBuilder.and(list.toArray(p));
                }
                , pageable3);
        Page<BomReviewEx> page3 = pages2.map(bomReviewExMapper::toEntity);

        Map<Object, Object> maps = new HashMap<>();
        maps.put("overview", page1);
        maps.put("review", page2);
        maps.put("reviewWithoutPost", page3);
        return maps;
    }

}
